package com.zhiyou100.javawebreview.jdbc.learn;

import java.sql.*;

/**
 * @packageName: MyUtilOfMySQL
 * @className: com.zhiyou100.javawebreview.jdbc.learn
 * @Description: TODO JDBC 工具类
 * @author: yang
 * @date: 7/14/20
 */
public class MyTool {
    {
        try {
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("加载驱动失败");
        }
    }

    private static final String IP = "192.168.217.82";
    private static final String PORT = "3306";
    private static final String DATABASE_NAME = "db_26";
    private static final String USER_NAME = "root";
    private static final String USER_PASSWORD = "1";
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DATABASE_NAME;

    /**
     * @Description: TODO 获取连接
     * @name: getConnection
     * @param: []
     * @return: java.sql.Connection
     * @date: 7/14/20 17:55
     * @author: yang
     */
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(URL, USER_NAME, USER_PASSWORD);
        } catch (SQLException throwable) {
            throw new RuntimeException("获取连接失败");
        }
    }

    /**
     * @Description: TODO 关闭  JDBC
     * @name: close
     * @param: [resultSet, statement, connection]
     * @return: void
     * @date: 7/14/20 19:07
     * @author: yang
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet == null) {
            // 如果 resultSet 为 null，关闭 后两个
            close(statement, connection);
            return;
        }
        if (statement == null || connection == null) {
            throw new RuntimeException("statement 或者 connection 为 null");
        }
        try {
            resultSet.close();
        } catch (SQLException throwable) {
            throw new RuntimeException("resultSet 关闭失败");
        }
        try {
            statement.close();
        } catch (SQLException throwable) {
            throw new RuntimeException("statement 关闭失败");
        }
        try {
            connection.close();
        } catch (SQLException throwable) {
            throw new RuntimeException("connection 关闭失败");
        }
    }


    /**
     * @Description: TODO 关闭 JDBC
     * @name: close
     * @param: [statement, connection]
     * @return: void
     * @date: 7/14/20 19:07
     * @author: yang
     */
    public static void close(Statement statement, Connection connection) {
        try {
            statement.close();
        } catch (SQLException throwable) {
            throw new RuntimeException("statement 关闭失败");
        }
        try {
            connection.close();
        } catch (SQLException throwable) {
            throw new RuntimeException("connection 关闭失败");
        }
    }


    public static void main(String[] args) throws SQLException {
//        Connection connection = getConnection();
//        Statement statement = connection.createStatement();
//        String sql = "select  * from student";
//        ResultSet resultSet = statement.executeQuery(sql);
//        while (resultSet.next()) {
//            int sid = resultSet.getInt("sid");
//            String sname = resultSet.getString("sname");
//            char sex = resultSet.getString("sex").charAt(0);
//            int sage = resultSet.getInt("sage");
//            int scid = resultSet.getInt("scid");
//            Student student = new Student(sid, sname, sex, sage, scid);
//            System.out.println(student);
//        }
//        close(resultSet, statement, connection);

//        Connection connection = getConnection();
//        Statement statement = connection.createStatement();
//        String sql = "alter table tab_3 drop tname ";
//        int i = statement.executeUpdate(sql);
//        System.out.println(i);
//        close(statement, connection);

        // 获取一个
        Student one = getOne(1);
        System.out.println(one);

    }

    /**
     * @Description 获取一个
     * @param sid
     * @return student
     * @throws SQLException
     */
    private static Student getOne(int sid) throws SQLException {
        Student student = new Student();
        Connection connection = getConnection();
        // 准备 sql 模版
        String sql = "select * from student where sid=?";
        // 发送 sql 模版，获取预编译对象
        PreparedStatement statement = connection.prepareStatement(sql);
        // 设置 模版的的参数
        statement.setInt(1, sid);
        // 执行查询语句，获取结果集
        ResultSet resultSet = statement.executeQuery();
        // 处理结果集
        while (resultSet.next()) {
            sid = resultSet.getInt("sid");
            String sname = resultSet.getString("sname");
            char sex = resultSet.getString("sex").charAt(0);
            int sage = resultSet.getInt("sage");
            int scid = resultSet.getInt("scid");
            student = new Student(sid, sname, sex, sage, scid);

        }
        // 关闭连接
        close(resultSet, statement, connection);
        return student;
    }
}
